const mysql = require('mysql2/promise');
const { encryptPassword } = require('./utils/auth');
const { v4: uuidv4 } = require('uuid');

// 数据库配置
const dbConfig = {
  host: '38.207.185.69',
  port: 3306,
  user: 'ai',
  password: '1246295951aA',
  database: 'ai',
  waitForConnections: true,
  connectionLimit: 1,
  queueLimit: 0
};

// 创建数据库表的SQL语句
const CREATE_USERS_TABLE = `
CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(36) PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  salt VARCHAR(255) NOT NULL,
  create_time BIGINT NOT NULL,
  update_time BIGINT NOT NULL,
  last_login_time BIGINT
)
`;

const CREATE_ROLES_TABLE = `
CREATE TABLE IF NOT EXISTS roles (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  avatar VARCHAR(255),
  prompt TEXT,
  greeting TEXT,
  is_custom BOOLEAN DEFAULT TRUE,
  is_default BOOLEAN DEFAULT FALSE,
  is_adult BOOLEAN DEFAULT FALSE,
  model VARCHAR(255),
  user_id VARCHAR(36),
  create_time BIGINT NOT NULL,
  update_time BIGINT NOT NULL,
  age VARCHAR(255),
  gender VARCHAR(255),
  occupation VARCHAR(255),
  hobbies TEXT,
  appearance TEXT,
  personality TEXT,
  background TEXT,
  scenario TEXT,
  interaction_priorities TEXT,
  attention_points TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)
`;

const CREATE_CHAT_GROUPS_TABLE = `
CREATE TABLE IF NOT EXISTS chat_groups (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  user_id VARCHAR(36) NOT NULL,
  members TEXT,
  create_time BIGINT NOT NULL,
  update_time BIGINT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)
`;

const CREATE_GROUP_MEMBERS_TABLE = `
CREATE TABLE IF NOT EXISTS group_members (
  id VARCHAR(36) PRIMARY KEY,
  group_id VARCHAR(36) NOT NULL,
  member_id VARCHAR(36) NOT NULL,
  FOREIGN KEY (group_id) REFERENCES chat_groups(id) ON DELETE CASCADE,
  FOREIGN KEY (member_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_group_member (group_id, member_id)
)
`;

const CREATE_CHAT_HISTORY_TABLE = `
CREATE TABLE IF NOT EXISTS chat_history (
  id VARCHAR(36) PRIMARY KEY,
  user_id VARCHAR(36) NOT NULL,
  target VARCHAR(255) NOT NULL,
  content LONGTEXT NOT NULL,
  create_time BIGINT NOT NULL,
  update_time BIGINT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_user_target (user_id, target)
)
`;

// 默认角色数据
const DEFAULT_ROLES = [
  {
    id: uuidv4(),
    name: '智能助手',
    description: '一个全能的AI助手，可以回答各种问题',
    avatar: '/static/avatars/assistant.png',
    prompt: '你是一个专业、友好的助手，能够提供准确的信息和有用的建议。',
    greeting: '你好！我是智能助手，有什么可以帮到你的吗？',
    is_custom: false,
    is_default: true,
    is_adult: false,
    model: 'gpt-3.5-turbo',
    create_time: Date.now(),
    update_time: Date.now()
  },
  {
    id: uuidv4(),
    name: '心理咨询师',
    description: '提供心理健康咨询和情感支持',
    avatar: '/static/avatars/therapist.png',
    prompt: '你是一个富有同理心的心理咨询师，善于倾听、理解和提供情感支持。',
    greeting: '你好，很高兴见到你。感受如何？我在这里倾听你的想法和感受。',
    is_custom: false,
    is_default: true,
    is_adult: false,
    model: 'gpt-3.5-turbo',
    create_time: Date.now(),
    update_time: Date.now()
  },
  {
    id: uuidv4(),
    name: '职场导师',
    description: '提供职业发展和职场关系指导',
    avatar: '/static/avatars/career.png',
    prompt: '你是一位经验丰富的职场导师，能够提供职业规划、简历制作、面试技巧等专业建议。',
    greeting: '你好！我是你的职场导师。无论是职业规划还是工作挑战，我都可以给你提供指导。',
    is_custom: false,
    is_default: true,
    is_adult: true,
    model: 'gpt-4',
    create_time: Date.now(),
    update_time: Date.now(),
    age: '45',
    gender: '不限',
    occupation: '高级职业顾问',
    personality: '专业、积极、富有洞察力'
  }
];

// 初始化数据库
async function initDatabase() {
  let connection;
  
  try {
    // 连接数据库
    connection = await mysql.createConnection(dbConfig);
    console.log('数据库连接成功');
    
    // 创建表
    console.log('开始创建数据库表...');
    
    await connection.execute(CREATE_USERS_TABLE);
    console.log('users表创建成功');
    
    await connection.execute(CREATE_ROLES_TABLE);
    console.log('roles表创建成功');
    
    await connection.execute(CREATE_CHAT_GROUPS_TABLE);
    console.log('chat_groups表创建成功');
    
    await connection.execute(CREATE_GROUP_MEMBERS_TABLE);
    console.log('group_members表创建成功');
    
    await connection.execute(CREATE_CHAT_HISTORY_TABLE);
    console.log('chat_history表创建成功');
    
    // 创建初始管理员用户
    console.log('开始创建管理员用户...');
    
    // 检查管理员是否已存在
    const [adminUsers] = await connection.execute(
      'SELECT * FROM users WHERE username = ?',
      ['admin']
    );
    
    if (adminUsers.length === 0) {
      // 加密密码
      const { hash, salt } = encryptPassword('admin123');
      const now = Date.now();
      const adminId = uuidv4();
      
      // 创建管理员
      await connection.execute(
        'INSERT INTO users (id, username, password, salt, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?)',
        [adminId, 'admin', hash, salt, now, now]
      );
      
      console.log('管理员用户创建成功');
    } else {
      console.log('管理员用户已存在，跳过创建');
    }
    
    // 获取管理员ID
    const [adminResult] = await connection.execute(
      'SELECT id FROM users WHERE username = ?',
      ['admin']
    );
    
    const adminId = adminResult[0].id;
    
    // 添加默认角色
    console.log('开始添加默认角色...');
    
    // 检查是否已有默认角色
    const [defaultRoles] = await connection.execute(
      'SELECT * FROM roles WHERE is_default = ?',
      [true]
    );
    
    if (defaultRoles.length === 0) {
      for (const role of DEFAULT_ROLES) {
        const roleFields = Object.keys(role).join(', ');
        const placeholders = Object.keys(role).map(() => '?').join(', ');
        const values = Object.values(role);
        
        await connection.execute(
          `INSERT INTO roles (${roleFields}) VALUES (${placeholders})`,
          values
        );
      }
      
      console.log('默认角色添加成功');
    } else {
      console.log('默认角色已存在，跳过创建');
    }
    
    console.log('数据库初始化完成');
    
  } catch (error) {
    console.error('数据库初始化失败:', error);
  } finally {
    if (connection) {
      await connection.end();
      console.log('数据库连接已关闭');
    }
  }
}

// 执行初始化
initDatabase(); 